package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Customer;

public class DbCustomer implements IfDbCustomer
{
	private Connection con;
	
	public DbCustomer()
	{
		con = DbConnection.getInstance().getDBcon();
	}

	public ArrayList<Customer> getAllCustomers(boolean retriveAssociation) 
	{
        return miscWhere("", retriveAssociation);
	}

	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<Customer> list = new ArrayList<Customer>();	
		
	    String query =  buildQuery(wClause);
  
        try
        {
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
        	while( results.next() )
        	{
        		Customer customerObject = new Customer();
        		customerObject = buildCustomer(results);	
                list.add(customerObject);	
        	}
            stmt.close();     
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer customerObject = new Customer();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try
		{ 
			// read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
                customerObject = buildCustomer(results);
                stmt.close();
			}
            else
            {
                customerObject = null;
            }
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return customerObject;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT name, address, zipCode, phoneNr, customerGroup FROM Customer";
		
		if (wClause.length()>0)
		{
			query=query+" WHERE "+ wClause;
		}			
		return query;
	}

	private Customer buildCustomer(ResultSet results)
    {   
		Customer customerObject = new Customer();
        try
        {
            customerObject.setName(results.getString("name"));
            customerObject.setAddress(results.getString("address"));
            customerObject.setZipCode(results.getString("zipcode"));
            customerObject.setPhoneNr(results.getString("phoneNr"));
            customerObject.setCustomerGroup(results.getBoolean("customerGroup"));
        }
        catch(Exception e)
        {
            System.out.println("There is a failure in building the Customer");
        }
        return customerObject;
    }
	
	public Customer findCustomerByName(String name, boolean retriveAssociation) 
	{
		String wClause = "Name = '" + name + "'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}

	public int insertCustomer(Customer customer) throws Exception 
	{
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(CustomerID) from Customer");
        nextID = nextID + 1;
        System.out.println("next ID = " + nextID);
		
		int rc = -1;
		String query="INSERT INTO Customer(customerid, name, address, zipcode, phonenr, customergroup) VALUES('"+
				nextID  + "','"  +
				customer.getName()  + "','"  +
			    customer.getAddress()  + "','"  +
		  		customer.getZipCode() + "','" +
			    customer.getPhoneNr() + "','" +
		  		customer.getCustomerGroup() +  "')";
		System.out.println("Test: " + query);
		try
		{ 
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	    }
	    catch(SQLException ex)
	    {
	        System.out.println("Customer is not created");
	        throw new Exception ("Customer is not inserted correct");
	    }
	    return(rc);
	}

	public int updateCustomer(Customer customer) 
	{
		Customer customerObject = customer;
		int lol = -1;

		String query="UPDATE Customer SET "+
		 	  "Name = '"+ customerObject.getName() +"', " +
		 	  "Address = '"+ customerObject.getAddress() +"', " +
              "ZipCode = '"+ customerObject.getZipCode() +"', " +
              "PhoneNr = '"+ customerObject.getPhoneNr() +"', " +
              "CustomerGroup = '"+ customerObject.getCustomerGroup() +"'" +
		      " WHERE Name = '"+ customerObject.getName() +"'";
              System.out.println("Update query: " + query);
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	lol  = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex)
	 	{
	 	 	System.out.println("Update exception in Customer db: " + ex);
	  	}
		return(lol);
	}

	public int deleteCustomerByName(String name)
	{
        int lol = -1;

        String query="DELETE FROM Customer WHERE Name = '"+ name +"'";
        System.out.println(query);
	  	try
	  	{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	lol = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	    catch(Exception ex)
   	    {
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	    }
		return(lol);
	}
}
